/**
 * 
 */
package com.store;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jedi.BaseObj;
import com.tss.util.DbConn;
import com.tss.util.DbRs;

/**
 * @author wevjoso
 *
 */
public class PalamvMgr extends BaseObj{
	
	
	public List get(String otherSQL,String orderField,int pageNo, int pageSize) {
		clearErr();
		DbRs rs = null;
		DbConn conn = null;
		try {
			conn = new DbConn();
			String sqlCount = "select count(*) as total from palamv where 1=1";
			if (otherSQL != null && !otherSQL.trim().equals(""))
				sqlCount += otherSQL;
			conn.prepare(sqlCount);
			DbRs rsCount = conn.executeQuery();
			int total = getInt(rsCount,0,"total");
			setTotal(total);
			boolean b = total % pageSize == 0;
			int pageCount = b ? (total / pageSize) : (total / pageSize + 1);
			if (pageNo > pageCount) pageNo = pageCount;
			String sql = "select *  from palamv where 1=1 ";
			if (otherSQL != null && !otherSQL.trim().equals(""))
				sql += otherSQL;
			if (orderField != null && !orderField.trim().equals(""))
				sql += " order by " + orderField;
			conn.prepare(sql);
			rs = conn.executeQuery(pageNo, pageSize);
		} catch (SQLException ex) {
			ex.printStackTrace();
			setErr(ex.getMessage());
			return new ArrayList();
		} catch (Exception e) {
			e.printStackTrace();
			setErr(e.getMessage());
			return new ArrayList();
		} finally {
			conn.close();
		}
		List r = new ArrayList();
		if (rs == null || rs.size() <= 0)
			return r;
		for (int i = 0; i < rs.size(); i++) {
			Palamv palav = new Palamv();
			palav.setComid(getInt(rs, i, "comid"));
			palav.setComname(get(rs, i, "comname"));
			palav.setConid(getInt(rs, i, "conid"));
			palav.setContype(get(rs, i, "contype"));
			palav.setCosts(getDouble(rs, i, "costs"));
			palav.setFifee(getDouble(rs, i, "fifee"));
			palav.setHoldfee(getDouble(rs, i, "holdfee"));
			palav.setIncome(getDouble(rs, i, "income"));
			palav.setInfee(getDouble(rs, i, "infee"));
			palav.setM(getInt(rs, i, "m"));
			palav.setY(getInt(rs, i, "y"));
			palav.setMfee(getDouble(rs, i, "mfee"));
			palav.setMyear(getInt(rs, i, "myear"));
			// palav.setId(getDouble(rs,i,"id"));
			palav.setOutefee(getDouble(rs, i, "outefee"));
			palav.setPrjname(get(rs, i, "prjname"));
			palav.setSpartfee(getDouble(rs, i, "spartfee"));
			palav.setTripfee(getDouble(rs, i, "tripfee"));
			palav.setSigfee(getDouble(rs, i, "signfee"));
			palav.setUser_id(get(rs, i, "user_id"));
			palav.setUser_name(get(rs, i, "user_name"));
			r.add(palav);
		}
		return r;
	}
	public List get(String otherSQL,String orderField) {
		clearErr();
		DbConn conn = new DbConn();
		DbRs rs = null;
		try {
			String sql = "";
			sql = "select p1.* from palav p1 inner join palmonthv p2 on p1.y = p2.y and p1.m=p2.m ";
			if (otherSQL != null && !otherSQL.trim().equals("")) 
				sql += otherSQL;
			if (orderField != null && !orderField.trim().equals(""))
				sql += " order by " + orderField;
			conn.prepare(sql);
			rs = conn.executeQuery();
		}
		catch(Exception ex) {
			ex.printStackTrace();
			setErr(ex.getMessage());
			return null;
		}
		finally {
			conn.close();
		}
		if (rs != null && rs.size() >0) {
			List r = new ArrayList();
			for(int i=0;i<rs.size();i++) {
				Palamv palav = new Palamv();
				palav.setComid(getInt(rs, i, "comid"));
				palav.setComname(get(rs, i, "comname"));
				palav.setConid(getInt(rs, i, "conid"));
				palav.setContype(get(rs, i, "contype"));
				palav.setCosts(getDouble(rs, i, "costs"));
				palav.setFifee(getDouble(rs, i, "fifee"));
				palav.setHoldfee(getDouble(rs, i, "holdfee"));
				palav.setIncome(getDouble(rs, i, "income"));
				palav.setInfee(getDouble(rs, i, "infee"));
				palav.setM(getInt(rs, i, "m"));
				palav.setY(getInt(rs, i, "y"));
				palav.setMfee(getDouble(rs, i, "spartfee"));
				palav.setMyear(getInt(rs, i, "myear"));
				palav.setOutefee(getDouble(rs, i, "outefee"));
				palav.setPrjname(get(rs, i, "prjname"));
				palav.setSpartfee(getDouble(rs, i, "spartfee"));
				palav.setTripfee(getDouble(rs, i, "tripfee"));
				palav.setSigfee(getDouble(rs, i, "signfee"));
				palav.setUser_id(get(rs, i, "user_id"));
				palav.setUser_name(get(rs, i, "user_name"));
				r.add(palav);
			}
			return r;
		}
		return null;
	}

}
